from selenium import webdriver
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.common.by import By
from time import sleep
from pyquery import PyQuery as pq
import pymysql


class MyMySql:
    def __init__(self):
        # 配置数据库参数，换上自己的
        self.conn = pymysql.connect(host='localhost',
                                    user='root',
                                    password='',
                                    db='jd',    # 先创建这个库才能连上
                                    charset='utf8')
        self.cursor = self.conn.cursor()

    # 建表
    def create_table(self):
        # 字段有店铺、价格、评论数、链接、介绍、标签、品牌
        # 建表如下
        # 店表(店铺、标签)
        # 商品表(商品类型、价格、评论数、介绍、链接、品牌)
        # 中间表(链接、店铺)
        try:
            sql = '''
                create table table_mid (
                id int auto_increment primary key,
                url varchar(255) not null
                )engine=innodb default charset=utf8
            '''
            self.cursor.execute(sql)
            self.conn.commit()
            print('中间表建立完成!')
        except Exception as e:
            print(e)

        try:
            sql = '''
                create table table_shop (
                id int auto_increment primary key,
                url varchar(255) not null,
                shop varchar(255) not null default '',
                tag varchar(255) not null default ''
                )engine=innodb default charset=utf8
            '''
            self.cursor.execute(sql)
            self.conn.commit()
            print('店表建立完成!')
        except Exception as e:
            print(e)

        try:
            sql = '''
                create table table_commodity (
                id int not null auto_increment primary key,
                url varchar(255) not null,
                kind varchar(255) not null,
                price float(10,2) not null,
                comments varchar(255) not null default '0',
                info text not null,
                brand varchar(255) not null
                )engine=innodb default charset=utf8
            '''
            self.cursor.execute(sql)
            self.conn.commit()
            print('商品表建立完成!')
        except Exception as e:
            print(e)
        
    # 添加外键
    def add_foreign(self):
        try:
            sql = '''
                alter table table_commodity add constraint FK_ID1 foreign key (id) references table_mid(id)
            '''
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)
        try:
            sql = '''
                alter table table_shop add constraint FK_ID2 foreign key (id) references table_mid(id)
            '''
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            print(e)

    # 插入
    def to_mysql(self, shop_list, commodity_list, mid_list):
        try:
            sql1 = "insert ignore into table_shop (url,shop,tag) values(%s,%s,%s)"  # 要插入的数据
            self.cursor.executemany(sql1, shop_list)  # 执行批量插入，大幅提升效率
            sql2 = "insert ignore into table_commodity (url,kind,price,comments,info,brand) values(%s,%s,%s,%s,%s,%s)"
            self.cursor.executemany(sql2, commodity_list)
            sql3 = "insert ignore into table_mid (url) values(%s)"
            self.cursor.executemany(sql3, mid_list)
            self.conn.commit()
            print('插入完成')
        except Exception as e:
            print(e)

    # 查询
    def find_data(self, text, flag=1):    # 默认模糊查找
        if flag:
            try:
                sql = "SELECT * FROM {} WHERE {} LIKE '%%{}%%'".format(text[0], text[1], text[2])
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                return result
            except Exception as e:
                print(e)
        else:
            try:
                sql = "SELECT * FROM {} WHERE {}='{}'".format(text[0], text[1], text[2])
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                return result
            except Exception as e:
                print(e)

    # 关闭
    def close_mysql(self):
        self.cursor.close()
        self.conn.close()


class JD:
    def __init__(self):
        options = webdriver.ChromeOptions()
        # 无头模式启动,即不打开浏览器
        # options.add_argument('--headless')
        # 加上这个属性规避bug
        # options.add_argument('--disable-gpu')
        # 不加载图片
        # options.add_argument('blink-settings=imagesEnabled=false')
        # self.browser = webdriver.Chrome(r'C:\Program Files (x86)\Google\Chrome\Application\chromedriver.exe', options=options)
        # 实例化数据库
        self.mysql = MyMySql()
        # 给数据库建表
        self.mysql.create_table()

    def open_browser(self, url, kw):
        # 打开京东
        self.browser.get(url)
        wait = WebDriverWait(self.browser, 10)
        print('准备输入关键字搜索————%s' % kw)
        # 等待输入框和按钮加载可用
        a = wait.until(EC.presence_of_element_located((By.ID, 'key')))
        while 1:
            if a:
                a.send_keys(kw)
                break
        b = wait.until(EC.element_to_be_clickable((By.CLASS_NAME, 'button')))
        while 1:
            if b:
                b.click()
                break
        for i in range(1, 81):
            print('正在解析%s第%d页......' % (kw, i), end='\t')
            # 向下滑动模拟浏览
            sleep(.5)
            js1 = "var q=document.documentElement.scrollTop=5000"
            self.browser.execute_script(js1)
            sleep(.5)
            js2 = "var q=document.documentElement.scrollTop=11000"
            self.browser.execute_script(js2)
            # 浏览完了解析保存
            html = self.browser.page_source
            self.parser(html, kw)
            # 等待翻页
            c = wait.until(EC.presence_of_element_located((By.XPATH, '//*[@id="J_bottomPage"]/span[2]/input')))
            while 1:
                if c:
                    break
            # 翻下一页
            input_page = self.browser.find_element_by_xpath('//*[@id="J_bottomPage"]/span[2]/input')
            input_page.clear()
            flag = 1
            while flag:
                try:
                    input_page.send_keys(i + 1)
                    flag = 0
                except Exception as e:
                    print(e)
            self.browser.find_element_by_xpath('//*[@id="J_bottomPage"]/span[2]/a').click()

    def parser(self, html, kw):
        # 解析
        doc = pq(html)
        tmp = doc('.gl-warp.clearfix .gl-item').items()   # 该对象为生成器
        tmp1, tmp2, tmp3 = [], [], []
        for item in tmp:
            # 批量插入数据时必须采用元组
            shop = item.find('.J_im_icon a').text()
            url = item.find('.p-name.p-name-type-2 a').attr('href')
            if not 'https' in url:
                url = 'https:' + url
            tmp1.append((url, shop, item.find('.p-icons i').text()))
            info = item.find('.p-name.p-name-type-2 a em').text()
            price = item.find('.p-price strong i').text()
            if not price:
                price = '0.00'
            comments = item.find('.p-commit strong a').text()
            if not comments:
                comments = '无'
            if '联想' in shop+info or 'Lenovo' in info:
                brand = '联想'
            elif '戴尔' in shop+info or 'Dell' in info:
                brand = '戴尔'
            elif '华为' in shop+info or 'HUAWEI' in info:
                brand = '华为'
            elif '惠普' in shop+info or 'HP' in info:
                brand = '惠普'
            elif '苹果' in shop+info or 'Apple' in info:
                brand = '苹果'
            elif '华硕' in shop+info or 'ASUS' in info:
                brand = '华硕'
            elif '小米' in shop+info or 'MI' in info:
                brand = '小米'
            elif '宏碁' in shop+info or 'acer' in info:
                brand = '宏碁'
            elif '外星人' in shop+info or 'Alienware' in info:
                brand = '外星人'
            else:
                brand = '其他'
            tmp2.append((url, kw, eval(price), comments, info, brand))
            tmp3.append((url,))
        self.mysql.to_mysql(tmp1, tmp2, tmp3)


if __name__ == '__main__':
    url = 'https://www.jd.com/'
    # 搜索的关键字，主要得凑满3W数据
    kw_ls = ['笔记本电脑', '台式电脑', '平板电脑', 'macbook', 'thinkpad', '树莓派', '服务器']
    # kw_ls = ['戴尔', '联想', '惠普', '华为', 'Apple', '华硕', '小米', '宏碁', '外星人']
    jd = JD()
    for kw in kw_ls:
        jd.open_browser(url, kw)
    print('所有数据爬取及插入完毕')
    jd.browser.close()
    jd.mysql.add_foreign()
    # 简单查询一下
    flag = int(input('是否模糊查询(回车或1为模糊，0精确)：'))
    a = input('输入查询表名：')
    b = input('输入查询字段：')
    c = input('输入查询关键字：')
    result = []
    if flag:
        result = jd.mysql.find_data([a, b, c])
    else:
        result = jd.mysql.find_data((a, b, c), flag=0)
    if result:
        for i in result:
            print(i)
    else:
        print('未查到相应数据')
    # 关闭数据库连接
    jd.mysql.close_mysql()
